﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SHJunketingModel;

namespace SHJunketingDAL
{
   public  class C_YuyueTypeService
    {

       public static SHJunketingModel.C_YuyueType AddC_YuyueTypeInfo(SHJunketingModel.C_YuyueType c_YuyueType)
       {
           string sql =
               "INSERT YuyueType (Name,starttime,endtime,fenlei)" +
               "VALUES (@Name,@starttime,@endtime,@fenlei)";

           sql += " ; SELECT @@IDENTITY";

           try
           {
               SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@Name", c_YuyueType.Name),
                    new SqlParameter("@starttime", c_YuyueType.Starttime),
                    new SqlParameter("@endtime", c_YuyueType.Endtime),
                    new SqlParameter("@fenlei", c_YuyueType.Fenlei)
					
				};

               int newId = DBHelper.GetScalar(sql, para);
               return GetC_YuyueTypeById(newId);
           }
           catch (Exception e)
           {
               Console.WriteLine(e.Message);
               throw e;
           }
       }


       public static SHJunketingModel.C_YuyueType GetC_YuyueTypeById(int Id)
       {
           string sql = "SELECT * FROM YuyueType WHERE Id = @Id";

            try
            {
                SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", Id));
                if (reader.Read())
                {
                    SHJunketingModel.C_YuyueType c_YuyueType = new SHJunketingModel.C_YuyueType();

                    c_YuyueType.ID = (int)reader["id"];
                    c_YuyueType.Name  =reader["name"].ToString();
                    c_YuyueType.Starttime = reader["starttime"].ToString();
                    c_YuyueType.Endtime = reader["endtime"].ToString();
                    c_YuyueType.Fenlei = reader["fenlei"].ToString();

                    reader.Close();

                    return c_YuyueType;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }
        }

       public static void DeleteC_YuyueTypeInfoByRoleId(int Id)
       {
           string sql = "DELETE C_UserRoleInfo WHERE Id = @Id";

           try
           {
               SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@Id", Id)
				};

               DBHelper.ExecuteCommand(sql, para);
           }
           catch (Exception e)
           {
               Console.WriteLine(e.Message);
               throw e;
           }
       }

       public static void ModifyC_YuyueTypeInfo(C_YuyueType c_YuyueType)
       {
           string sql =
               "UPDATE YuyueType " +
               "SET " +
                   "Name = @Name, " +
                   "starttime = @starttime ," +
                   "endtime = @endtime ," +
                   "fenlei = @fenlei " +
               "WHERE Id = @Id";


           try
           {
               SqlParameter[] para = new SqlParameter[]
				{
                    new SqlParameter("@Name", c_YuyueType.Name),
					new SqlParameter("@Id", c_YuyueType.ID ),
                     new SqlParameter("@starttime", c_YuyueType.Starttime),
					new SqlParameter("@endtime", c_YuyueType.Endtime ),
                     new SqlParameter("@fenlei", c_YuyueType.Fenlei),

				};

               DBHelper.ExecuteCommand(sql, para);
           }
           catch (Exception e)
           {
               Console.WriteLine(e.Message);
               throw e;
           }

       }		


       /// <summary>
       /// 删除多条
       /// </summary>
       /// <param name="userId"></param>
       /// <returns></returns>
       /// 创建者：邹纳
       public static int DeleteC_YuyueTypeInfoByRoleId(string Id)
       {
           string sql = "DELETE YuyueType WHERE id in (" + Id + ")";
           int liRowCount = -1;
           try
           {
               liRowCount = DBHelper.ExecuteCommand(sql);
           }
           catch (Exception e)
           {
               Console.WriteLine(e.Message);
               throw e;
           }
           return liRowCount;
       }


       public static IList<C_YuyueType> GetAllC_YuyueType()
       {
           string sqlAll = "SELECT * FROM YuyueType";
           return GetC_YuyueTypeBySql(sqlAll);
       }

       private static IList<C_YuyueType> GetC_YuyueTypeBySql(string safeSql)
       {
           List<C_YuyueType> list = new List<C_YuyueType>();

           try
           {
               DataTable table = DBHelper.GetDataSet(safeSql);

               foreach (DataRow row in table.Rows)
               {
                   C_YuyueType c_YuyueType = new C_YuyueType();

                   c_YuyueType.ID = (int)row["id"];
                   c_YuyueType.Name = row["fenlei"].ToString();

                   c_YuyueType.Starttime = row["starttime"].ToString();
                   c_YuyueType.Endtime = row["endtime"].ToString();

                   list.Add(c_YuyueType);
               }

               return list;
           }
           catch (Exception e)
           {
               Console.WriteLine(e.Message);
               throw e;
           }

       }

       public static DataTable GetYuyueTypeInfo(string fenlei)
       {
           string sql = "select id,starttime,endtime,fenlei from YuyueType";
           if (fenlei != string.Empty)
           {
               sql += " where fenlei LIKE '%" + fenlei + "%'";
           }
           return DBHelper.GetDataSet(sql);
       }
    }
}
